/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package net.phoenix.repository.dao.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.MessageFormat;

import net.phoenix.repository.dao.ValueTableCreateDao;
import net.phoenix.repository.dao.QDBName;

/**
 * Jigsaw server
 * 
 * @author    <a href="shamphone@gmail.com">Li XiongFeng</a>
 * @date      2012-10-25
 * @version   1.0.0
 */
public class MysqlValueTableCreateDao extends ValueTableCreateDao {
	private static final MessageFormat SQL_ADD_COLUMN = new MessageFormat("ALTER TABLE {0} ADD COLUMN {1} {2}");
	private static final MessageFormat SQL_CREATE_INDEX = new MessageFormat("CREATE INDEX {0} ON {1}({2})");
	private static final String [] PROPERTY_TYPE_2_SQL_TYPE ={"VARCHAR(1024)", "VARCHAR(1024)","LONGBLOB","BIGINT","DOUBLE","DATETIME","BOOLEAN","VARCHAR(1024)","VARCHAR(1024)","VARCHAR(128)"}; 


	/**
	 * Add new Column to Table
	 * @param nodeDefinitionName
	 * @param propertyDefinitionName
	 * @param multiple
	 * @param searchable
	 * @param type
	 * @throws SQLException
	 */
	@Override
	public void addColumn(String nodeDefinitionName, String propertyDefinitionName, boolean multiple, boolean searchable, int type) throws SQLException {
		int count = 1;
		if (multiple)
			count = MULTIPLE_MAX;
		for (int i = 0; i < count; i++) {
			String table = QDBName.toMysqlTableName(nodeDefinitionName);
			String column = QDBName.toMysqlColumnName(propertyDefinitionName, i);
			if (!this.isColumnExists(table, column)) {
				String query = SQL_ADD_COLUMN.format(new Object[] {table, column, PROPERTY_TYPE_2_SQL_TYPE[type] });
				PreparedStatement command = null;
				try {
					command = connection.prepareStatement(query);
					command.execute();
				} finally {
					this.close(command);
				}
				if(searchable){
					query = SQL_CREATE_INDEX.format(new Object[] { QDBName.toMysqlIndexName(nodeDefinitionName, propertyDefinitionName, count), QDBName.toMysqlTableName(nodeDefinitionName), column });
					command = null;
					try {
						command = connection.prepareStatement(query);
						command.execute();
					} catch(SQLException ex){
						log.error("Error in create index:"+ query+", "+ ex.getMessage() );
					}finally {				
						this.close(command);
					}
					
				}
			}
		}

	}
	
	
	private static final MessageFormat CREATE_TABLE = new MessageFormat("CREATE TABLE `{0}` (pkid varchar(128) NOT NULL, PRIMARY KEY(pkid))");
	
	/**
	 * 
	 * @param tableName
	 * @throws SQLException
	 */
	@Override
	public void createTable(String tableName) throws SQLException {
		String query = CREATE_TABLE.format(new Object[] {  tableName });
		PreparedStatement command = null;
		try {
			command = connection.prepareStatement(query);
			command.execute();
		} finally {
			this.close(command);
		}
	}
	

	/* (non-Javadoc)
	 * @see net.phoenix.repository.dao.NodeTypeDao#isTableExists(java.lang.String)
	 */
	@Override
	public boolean isTableExists(String tableName) {		
		try {
			String query = "SELECT * FROM " +  QDBName.toMysqlTableName(tableName) + " WHERE 1=0";
			PreparedStatement statement = connection.prepareStatement(query);
			ResultSet rs = statement.executeQuery();
			rs.close();
			statement.close();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}
	
	
	public boolean isColumnExists(String table, String columnName) throws SQLException {
					
		String column = columnName;
		if(column.indexOf('`')>=0)
			column = column.substring(1, column.length()-1);		
			
		ResultSetMetaData rs = this.getMetaData(table);
		for (int i = 1; i < rs.getColumnCount()+1; i++)
			if (rs.getColumnName(i).equalsIgnoreCase(column))
				return true;
		return false;
	}
	
}
